package com.oro.dao.impl;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.LockMode;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oro.dao.StudentDao;
import com.oro.domain.Banca;
import com.oro.domain.BankBranch;
import com.oro.domain.Categoria;
import com.oro.domain.Contatto;
import com.oro.domain.Corso;
import com.oro.domain.Costo;
import com.oro.domain.IndirizzoIT;
import com.oro.domain.Iscrizione;
import com.oro.domain.MacroCategoria;
import com.oro.domain.MigrationAgent;
import com.oro.domain.Scuola;
import com.oro.domain.Sede;
import com.oro.domain.Studente;
import com.oro.domain.Traduttore;
import com.oro.domain.testManyToMany.A;
import com.oro.domain.testManyToMany.B;
import com.oro.domain.testOneToMany.Child;
import com.oro.domain.testOneToMany.Parent;

@SuppressWarnings("unchecked")
public class HibernateStudentDaoSupport extends HibernateDaoSupport implements
		StudentDao {

	
	@Override
	public void delete(Contatto c) {
		getHibernateTemplate().delete(c);

	}

	
	@Override
	public void delete(Corso course) {
		getHibernateTemplate().delete(course);

	}

	
	@Override
	public void delete(Iscrizione enr) {
		getHibernateTemplate().delete(enr);
	}

	
	@Override
	public void delete(Scuola school) {
		getHibernateTemplate().delete(school);

	}

	
	@Override
	public void delete(Studente stud) {
		getHibernateTemplate().delete(stud);

	}

	
	@Override
	public void deleteAs() {
		getHibernateTemplate().deleteAll(getAs());

	}

	
	@Override
	public void deleteBranches() {
		getHibernateTemplate().deleteAll(getBranches());

	}

	
	@Override
	public void deleteBs() {
		getHibernateTemplate().deleteAll(getBs());

	}

	
	@Override
	public void deleteCategories() {
		getHibernateTemplate().deleteAll(getCategories());

	}

	
	@Override
	public void deleteChildren() {
		getHibernateTemplate().deleteAll(
				getHibernateTemplate().find("from Parent"));

	}

	
	@Override
	public void deleteContact(Long id) {
		getHibernateTemplate().delete(this.getContact(id));

	}

	
	@Override
	public void deleteContacts() {
		getHibernateTemplate().deleteAll(this.getContacts());

	}

	
	@Override
	public void deleteCourse(Long id) {
		getHibernateTemplate().delete(this.getCorso(id));

	}

	
	@Override
	public void deleteCourses() {
		getHibernateTemplate().deleteAll(this.getCourses());

	}

	
	@Override
	public void deleteEnrollment(Long id) {
		getHibernateTemplate().delete(this.getEnrollment(id));

	}

	
	@Override
	public void deleteEnrollments() {
		getHibernateTemplate().deleteAll(this.getEnrollments());

	}

	
	@Override
	public void deleteMacroCategories() {
		getHibernateTemplate().deleteAll(getMacroCategories());

	}

	
	@Override
	public void deleteParents() {
		getHibernateTemplate().deleteAll(
				getHibernateTemplate().find("from Child"));

	}

	
	@Override
	public void deleteSchools() {
		getHibernateTemplate().deleteAll(this.getSchools());

	}

	
	@Override
	public void deleteStudent(Long id) {
		getHibernateTemplate().delete(this.getStudent(id));

	}

	
	@Override
	public void deleteStudents() {
		getHibernateTemplate().deleteAll(this.getStudents());

	}

	
	@Override
	public void deleteTelephones() {
		getHibernateTemplate().deleteAll(this.getTelephones());

	}

	
	@Override
	public List<A> getAs() {
		return getHibernateTemplate().find("from A");
	}

	
	@Override
	public Banca getBank(Long id) {
		return getHibernateTemplate().get(Banca.class, id);
	}

	
	@Override
	public List<BankBranch> getBankBranches(Banca bank) {
		return getHibernateTemplate()
				.find("select distinct branch from BankBranch branch where branch.bank = ?",
						bank);
	}

	
	@Override
	public Banca getBankByName(String name) {
		List<Banca> banche = getHibernateTemplate().find(
				"from Banca b where b.name = ?", name);
		if (banche.isEmpty())
			return null;
		else
			return banche.get(0);
	}

	
	@Override
	public List<String> getBankNames() {
		return getHibernateTemplate()
				.find("select distinct bank.name from Banca bank order by bank.name asc");
	}

	
	@Override
	public List<Banca> getBanks() {
		List<Banca> result = getHibernateTemplate().find(
				"select distinct bank from Banca bank order by bank.name asc");
		for (Banca b : result)
			prepare(b);
		return result;
	}

	
	@Override
	public Sede getBranch(Long id) {
		return getHibernateTemplate().get(Sede.class, id);
	}

	
	@Override
	public List<Sede> getBranches() {
		return getHibernateTemplate().find("from Sede");
	}

	
	@Override
	public List<B> getBs() {
		return getHibernateTemplate().find("from B");
	}

	
	@Override
	public List<Categoria> getCategories() {
		return getHibernateTemplate()
				.find("select distinct c from Categoria c");
	}

	
	@Override
	public List<Categoria> getCategoriesByMacroCategoryId(Long id) {
		return getHibernateTemplate().find(
				"from Categoria cat where cat.macroCategoria.id = ?", id);
	}

	
	@Override
	public Categoria getCategory(Long id) {
		return getHibernateTemplate().get(Categoria.class, id);
	}

	
	@Override
	public List<Child> getChildren() {
		return getHibernateTemplate().find("from Child");
	}

	
	@Override
	public Contatto getContact(Long id) {
		return getHibernateTemplate().get(Contatto.class, id);
	}

	
	@Override
	public List<Contatto> getContacts() {
		return getHibernateTemplate().find("from Contatto");
	}

	
	@Override
	public List<Contatto> getContactsByBranch(Sede sede) {
		return getHibernateTemplate()
				.find("select branch.contattiDiRiferimento from Sede branch where branch = ?",
						sede);
	}

	
	@Override
	public List<Contatto> getContactsByName(String name) {
		return getHibernateTemplate()
				.find("select distinct contact from Contatto contact where contact.firstName = ?",
						name);
	}

	
	@Override
	public List<Contatto> getContactsByPhone(String number) {
		return getHibernateTemplate()
				.find("select distinct contact from Contatto contact join fetch contact.telephones telephone where telephone.number = ?",
						number);
	}

	
	@Override
	public List<Contatto> getContactsBySchool(Scuola school) {
		return getHibernateTemplate()
				.find("select school.contattiDiRiferimento from Scuola school where school = ?",
						school);
	}

	
	@Override
	public List<Contatto> getContactsByStudent(Studente stud) {
		return getHibernateTemplate()
				.find("select student.contattiDiEmergenza from Studente student where student = ?",
						stud);
	}

	
	@Override
	public List<Contatto> getContactsBySurname(String surname) {
		return getHibernateTemplate()
				.find("select distinct contact from Contatto contact where contact.surname = ?",
						surname);
	}

	
	@Override
	public Corso getCorso(Long id) {
		return getHibernateTemplate().get(Corso.class, id);
	}

	
	@Override
	public List<Costo> getCosts() {
		return getHibernateTemplate().find("from Costo");
	}

	
	@Override
	public List<Costo> getCostsByCourse(Long id) {
		return getHibernateTemplate()
				.find("select distinct cost from Costo cost where cost.course.id = ?",
						id);
	}

	
	@Override
	public List<Corso> getCourseByName(String name) {
		return getHibernateTemplate().find(
				"from Corso course where course.name = ?", name);
	}

	
	@Override
	public List<Corso> getCourses() {
		return getHibernateTemplate().find("from Corso");
	}

	
	@Override
	public List<Corso> getCoursesByBranch(Sede sede) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course join fetch course.sedi branch where branch = ?",
						sede);
	}

	
	@Override
	public List<Corso> getCoursesByCategory(Categoria category) {
		if (category != null)
			return this.getCoursesByCategory(category.getName());
		else
			return new ArrayList<Corso>();
	}

	
	@Override
	public List<Corso> getCoursesByCategory(String category) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course where course.categoria.name = ?",
						category);
	}

	
	@Override
	public List<Corso> getCoursesByMacroCategory(MacroCategoria macroCategory) {
		if (macroCategory != null)
			return this.getCoursesByMacroCategory(macroCategory.getName());
		else
			return new ArrayList<Corso>();
	}

	
	@Override
	public List<Corso> getCoursesByMacroCategory(String macroCategory) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course where course.categoria.macroCategoria.name = ?",
						macroCategory);
	}

	
	@Override
	public List<Corso> getCoursesBySchool(Scuola scuola) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course join fetch course.sedi branch where branch.school = ?",
						scuola);
	}

	
	@Override
	public List<Corso> getCoursesByState(String state) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course join course.sedi branch where branch.indirizzo.stato = ?",
						state);
	}

	
	@Override
	public List<Corso> getCoursesByTown(String town) {
		return getHibernateTemplate()
				.find("select distinct course from Corso course join course.sedi branch where branch.indirizzo.citta = ?",
						town);
	}

	
	@Override
	public Iscrizione getEnrollment(Long id) {
		return getHibernateTemplate().get(Iscrizione.class, id);
	}

	
	@Override
	public List<Iscrizione> getEnrollments() {
		return getHibernateTemplate().find("from Iscrizione");
	}

	
	@Override
	public List<Iscrizione> getEnrollmentsByBranch(Sede sede) {
		return getHibernateTemplate()
				.find("select distinct enrollment from Iscrizione enrollment join enrollment.corso.sedi sede where sede = ?",
						sede);
	}

	
	@Override
	public List<Iscrizione> getEnrollmentsByCourse(Corso c) {
		return getHibernateTemplate().find(
				"from Iscrizione enrollment where enrollment.corso = ?", c);
	}

	
	@Override
	public List<Iscrizione> getEnrollmentsBySchool(Scuola s) {
		return getHibernateTemplate()
				.find("select distinct enrollment from Iscrizione enrollment join enrollment.corso.sedi sede where sede.school = ?",
						s);
	}

	
	@Override
	public List<Iscrizione> getEnrollmentsByStudent(Studente stud) {
		return getHibernateTemplate().find(
				"from Iscrizione enrollment where enrollment.studente = ?",
				stud);
	}

	
	@Override
	public List<MacroCategoria> getMacroCategories() {
		return getHibernateTemplate().find("from MacroCategoria");
	}

	
	@Override
	public List<Parent> getParents() {
		return getHibernateTemplate().find("from Parent");
	}

	
	@Override
	public Scuola getSchool(Long id) {
		Scuola result = getHibernateTemplate().get(Scuola.class, id);
		prepare(result);
		return result;
	}

	
	@Override
	public List<Scuola> getSchools() {
		return getHibernateTemplate()
				.find("select school from Scuola school order by school.ragioneSociale");
	}

	
	@Override
	public List<Scuola> getSchoolsByCourseCategory(Categoria category) {
		if (category != null)
			return this.getSchoolsByCourseCategory(category.getName());
		else
			return new ArrayList<Scuola>();
	}

	
	@Override
	public List<Scuola> getSchoolsByCourseCategory(Long id) {
		return getHibernateTemplate()
				.find("select distinct branch.school from Corso course join course.sedi branch where course.categoria.id = ?",
						id);
	}

	
	@Override
	public List<Scuola> getSchoolsByCourseCategory(String category) {
		return getHibernateTemplate()
				.find("select distinct branch.school from Corso course join course.sedi branch where course.categoria.name = ?",
						category);
	}

	
	@Override
	public List<Scuola> getSchoolsByCourseMacroCategory(
			MacroCategoria macroCategory) {
		if (macroCategory != null)
			return this
					.getSchoolsByCourseMacroCategory(macroCategory.getName());
		else
			return new ArrayList<Scuola>();
	}

	
	@Override
	public List<Scuola> getSchoolsByCourseMacroCategory(String macroCategory) {
		return getHibernateTemplate()
				.find("select distinct branch.school from Corso course join course.sedi branch where course.categoria.macroCategoria.name = ?",
						macroCategory);
	}

	
	@Override
	public List<Scuola> getSchoolsByCoursePrice(float min, float max) {
		return getHibernateTemplate()
				.findByNamedParam(
						"select distinct school from Scuola school join school.branches branch join branch.corsi course join course.costi cost where cost.weeklyPrice >= :min and cost.weeklyPrice <= :max",
						new String[] { "min", "max" },
						new Object[] { min, max });
	}

	
	@Override
	public List<Scuola> getSchoolsByName(String name) {
		return getHibernateTemplate().find(
				"from Scuola school where school.ragioneSociale = ?", name);
	}

	
	@Override
	public List<Scuola> getSchoolsByState(String state) {
		return getHibernateTemplate().find(
				"from Scuola school where school.indirizzo.stato = ?", state);
	}

	
	@Override
	public List<Scuola> getSchoolsByTown(String town) {
		return getHibernateTemplate().find(
				"from Scuola school where school.indirizzo.citta = ?", town);
	}

	
	@Override
	public List<String> getSchoolsNames() {
		return getHibernateTemplate()
				.find("select distinct school.ragioneSociale from Scuola school order by school.ragioneSociale asc");
	}

	
	@Override
	public Studente getStudent(Long id) {
		return getHibernateTemplate().get(Studente.class, id);
	}

	
	@Override
	public List<Studente> getStudentiByName(String firstName) {
		return getHibernateTemplate()
				.find("select distinct stud from Studente stud where stud.firstName = ? order by stud.surname",
						firstName);
	}

	
	@Override
	public List<Studente> getStudentiByPhone(String number) {
		return getHibernateTemplate()
				.find("select distinct stud from Studente stud join fetch stud.telephones phone where phone.number = ? order by stud.firstName asc",
						number);
	}

	
	@Override
	public List<Studente> getStudentiByState(String state) {
		return getHibernateTemplate()
				.find("select distinct stud from Studente stud where stud.indirizzoAu.stato = ?",
						state);
	}

	
	@Override
	public List<Studente> getStudentiBySurname(String surname) {
		return getHibernateTemplate().find(
				"from Studente stud where stud.surname = ?", surname);
	}

	
	@Override
	public List<Studente> getStudentiByTown(String town) {
		Object[] params = { town, town };
		return getHibernateTemplate()
				.find("select distinct stud from Studente stud where stud in (from Studente sit where sit.indirizzoIt.citta = ?) or stud in (from Studente sau where sau.indirizzoAu.citta = ?))",
						params);
	}

	
	@Override
	public List<Studente> getStudents() {
		return getHibernateTemplate()
				.find("select distinct stud from Studente stud order by stud.firstName");
	}

	
	@Override
	public List<Studente> getStudentsBySchool(Long schoolId) {
		List<Studente> result = getHibernateTemplate()
				.find("select distinct stud from Studente stud join fetch stud.iscrizioni enrollment join enrollment.corso course join course.sedi branch where enrollment.studente = stud and branch.school.id = ?",
						schoolId);
		return result;
	}

	
	@Override
	public List<String> getStudentsEmails() {
		return getHibernateTemplate()
				.find("select email.email from Studente as stud join stud.emails as email");
	}

	
	@Override
	public List<String> getStudentsEmailsByTown(String studentTown) {
		return getHibernateTemplate()
				.find("select email.email from Studente as stud join stud.emails as email where stud.indirizzoIt.citta = ?",
						studentTown);
	}

	
	@Override
	public List<String> getStudentsNames() {
		return getHibernateTemplate()
				.find("select distinct stud.firstName from Studente stud order by stud.firstName asc");
	}

	
	@Override
	public List<String> getStudentsNamesByTown(String studentTown) {
		return getHibernateTemplate()
				.find("select distinct stud.firstName from Studente stud where stud.indirizzoIt.citta = ? order by stud.firstName asc",
						studentTown);
	}

	
	@Override
	public List<String> getStudentsSurnames() {
		return getHibernateTemplate()
				.find("select distinct stud.surname from Studente stud order by stud.surname asc");
	}

	
	@Override
	public List<String> getStudentsSurnamesByTown(String studentTown) {
		return getHibernateTemplate()
				.find("select distinct stud.surname from Studente stud where stud.indirizzoIt.citta = ? order by stud.surname asc",
						studentTown);
	}

	
	@Override
	public List<Studente> getTelephones() {
		return getHibernateTemplate().find("from Telephone");
	}

	
	@Override
	public List<String> getTownsAu() {
		return getHibernateTemplate()
				.find("select distinct ind.citta from IndirizzoAU ind order by ind.citta asc");
	}

	
	@Override
	public List<String> getTownsIt() {
		return getHibernateTemplate()
				.find("select distinct ind.citta from IndirizzoIT ind order by ind.citta asc");
	}

	
	@Override
	public void initialize(Object o) {
		getHibernateTemplate().initialize(o);

	}

	private void prepare(Banca b) {
		getHibernateTemplate().lock(b, LockMode.NONE);
		getHibernateTemplate().initialize(b.getBranches());
	}

	
	@Override
	public void prepare(Contatto c) {
		getHibernateTemplate().lock(c, LockMode.NONE);
		getHibernateTemplate().initialize(c.getEmails());
		getHibernateTemplate().initialize(c.getTelephones());
		getHibernateTemplate().initialize(c.getIndirizziIt());
		getHibernateTemplate().initialize(c.getIndirizziAu());
	}

	
	@Override
	public void prepare(Corso c) {
		getHibernateTemplate().lock(c, LockMode.NONE);
		getHibernateTemplate().initialize(c.getCategoria());
		getHibernateTemplate().initialize(c.getCosti());
		for (Costo co : c.getCosti())
			prepare(co);
		getHibernateTemplate().initialize(c.getIscrizioni());
		getHibernateTemplate().initialize(c.getSchool());
		getHibernateTemplate().initialize(c.getSedi());
		getHibernateTemplate().initialize(c.getStudyMaterial());
		getHibernateTemplate().initialize(c.getSubscriptionCommission());
		getHibernateTemplate().initialize(c.getTuitionCommission());
	}

	
	@Override
	public void prepare(Costo c) {
		getHibernateTemplate().lock(c, LockMode.NONE);
		getHibernateTemplate().initialize(c.getCourse());
		getHibernateTemplate().initialize(c.getSubscriptionCommission());
		getHibernateTemplate().initialize(c.getTuitionCommission());
	}

	
	@Override
	public void prepare(Scuola s) {
		getHibernateTemplate().lock(s, LockMode.NONE);
		getHibernateTemplate().initialize(s.getAdder());
		getHibernateTemplate().initialize(s.getBranches());
		for (Sede b : s.getBranches())
			prepare(b);
		getHibernateTemplate().initialize(s.getContattiDiRiferimento());
		for (Contatto c : s.getContattiDiRiferimento())
			prepare(c);
		getHibernateTemplate().initialize(s.getCourses());
		for (Corso c : s.getCourses())
			prepare(c);
		getHibernateTemplate().initialize(s.getEmails());
		getHibernateTemplate().initialize(s.getFaxes());
		getHibernateTemplate().initialize(s.getTelephones());
		getHibernateTemplate().initialize(s.getIndirizzo());
		getHibernateTemplate().initialize(s.getInsertionDate());
		getHibernateTemplate().initialize(s.getStudyMaterial());
	}

	
	@Override
	public void prepare(Sede b) {
		getHibernateTemplate().lock(b, LockMode.NONE);
		getHibernateTemplate().initialize(b.getContattiDiRiferimento());
		if (b.getContattiDiRiferimento().isEmpty())
			b.getContattiDiRiferimento().add(null);
		getHibernateTemplate().initialize(b.getCorsi());
		getHibernateTemplate().initialize(b.getEmails());
		getHibernateTemplate().initialize(b.getFaxes());
		getHibernateTemplate().initialize(b.getSchool());
		getHibernateTemplate().initialize(b.getServiziAggiunti());
		getHibernateTemplate().initialize(b.getTelephones());

	}

	
	@Override
	public void prepare(Studente studentInput) {
		getHibernateTemplate().lock(studentInput, LockMode.NONE);
		getHibernateTemplate().initialize(studentInput.getEmails());
		getHibernateTemplate().initialize(studentInput.getTelephones());
		getHibernateTemplate()
				.initialize(studentInput.getContattiDiEmergenza());
		getHibernateTemplate().initialize(studentInput.getIndirizziIt());
		getHibernateTemplate().initialize(studentInput.getIndirizziAu());
		getHibernateTemplate().initialize(studentInput.getStayDetails());
		for (Contatto c : studentInput.getContattiDiEmergenza()) {
			prepare(c);
		}
	}

	
	@Override
	public void refresh(Object o) {
		getHibernateTemplate().refresh(o);
	}

	
	@Override
	public void save(A a) {
		save((Object) a);

	}

	
	@Override
	public void save(B b) {
		save((Object) b);

	}

	
	@Override
	public void save(Banca bank) {
		if (bank.getId() != null && bank.getId() != 0L)
			getHibernateTemplate().merge(bank);
		else
			getHibernateTemplate().saveOrUpdate(bank);
		getHibernateTemplate().flush();

	}

	
	@Override
	public void save(BankBranch bankBranch) {
		if (bankBranch.getId() != null && bankBranch.getId() != 0L)
			getHibernateTemplate().merge(bankBranch);
		else
			getHibernateTemplate().saveOrUpdate(bankBranch);
		getHibernateTemplate().flush();
		getHibernateTemplate().evict(bankBranch);
	}

	
	@Override
	public void save(Categoria c1) {
		save((Object) c1);
	}

	
	@Override
	public void save(Child child) {
		save((Object) child);
	}

	
	@Override
	public void save(Contatto con) {
		save((Object) con);
	}

	
	@Override
	public void save(Corso corso) {
		save((Object) corso);
	}

	
	@Override
	public void save(Costo cost) {
		save((Object) cost);
	}

	
	@Override
	public void save(IndirizzoIT ind) {
		save((Object) ind);
	}

	
	@Override
	public void save(Iscrizione isc) {
		save((Object) isc);
	}

	
	@Override
	public void save(MacroCategoria mc1) {
		save((Object) mc1);
	}

	
	@Override
	public void save(MigrationAgent migrationAgentInput) {
		getHibernateTemplate().save(migrationAgentInput);

	}

	private void save(Object o) {
		getHibernateTemplate().saveOrUpdate(o);
		getHibernateTemplate().flush();
	}

	
	@Override
	public void save(Parent parent) {
		save((Object) parent);
	}

	
	@Override
	public void save(Scuola school) {
		save((Object) school);
	}

	
	@Override
	public void save(Sede branch) {
		save((Object) branch);
	}

	
	@Override
	public void save(Studente stud) {
		if (stud.getId() != null && stud.getId() != 0L)
			getHibernateTemplate().merge(stud);
		else
			getHibernateTemplate().saveOrUpdate(stud);
		getHibernateTemplate().flush();
		// save((Object) stud);
	}

	
	@Override
	public void save(Traduttore translatorInput) {
		getHibernateTemplate().save(translatorInput);

	}

	
	@Override
	public void SAVE(Iscrizione i) {
		SAVE((Object) i);
	}

	private Serializable SAVE(Object o) {
		Serializable s = getHibernateTemplate().save(o);
		getHibernateTemplate().flush();
		return s;
	}

	
	@Override
	public void SAVE(Studente stud) {
		SAVE((Object) stud);
	}

	
	@Override
	public List<Scuola> searchSchools(String schoolName, String schoolTown,
			Long courseCategoryId, float minPrice, float maxPrice) {

		String query = "select distinct school from Scuola school where school in ";
		String dummyQuery = "(select distinct school from Scuola school)";
		String schoolNameQuery = "(select distinct school from Scuola school where school.ragioneSociale = '"
				+ schoolName + "')";
		String schoolTownQuery = "(select distinct school from Scuola school where school in "
				+ "(select distinct school from Scuola school where school.indirizzo.citta = '"
				+ schoolTown
				+ "')"
				+ " or school in "
				+ "(select distinct school from Scuola school join school.branches branch where branch.indirizzo.citta = '"
				+ schoolTown + "')" + ")";
		String courseCategoryIdQuery = "(select distinct branch.school from Corso course join course.sedi branch where course.categoria.id = '"
				+ courseCategoryId + "')";
		String minPriceQuery = "(select distinct school from Scuola school where school in "
				+ "(select distinct school from Scuola school join school.branches branch join branch.corsi course where course.costi is empty)"
				+ " or school in "
				+ "(select distinct school from Scuola school join school.branches branch join branch.corsi course join course.costi cost where cost.weeklyPrice >= '"
				+ minPrice + "')" + ")";
		String maxPriceQuery = "(select distinct school from Scuola school where school in "
				+ "(select distinct school from Scuola school join school.branches branch join branch.corsi course where course.costi is empty)"
				+ " or school in "
				+ "(select distinct school from Scuola school join school.branches branch join branch.corsi course join course.costi cost where cost.weeklyPrice <= '"
				+ maxPrice + "')" + ")";

		query += dummyQuery;

		if (schoolName != null && schoolName != "") {
			query += " and school in ";
			query += schoolNameQuery;
		}

		if (schoolTown != null && schoolTown != "") {
			query += " and school in ";
			query += schoolTownQuery;
		}

		if (courseCategoryId != null && courseCategoryId != 0L) {
			query += " and school in ";
			query += courseCategoryIdQuery;
		}

		if (minPrice != 0) {
			query += " and school in ";
			query += minPriceQuery;
		}

		if (maxPrice != 0) {
			query += " and school in ";
			query += maxPriceQuery;
		}

		query += " order by school.ragioneSociale";

		System.out.println(query);
		
		List<Scuola> result = getHibernateTemplate().find(query);
		
		for (Scuola sc : result)
			prepare(sc);

		return result;
	}

	
	@Override
	public List<Studente> searchStudents(String firstName, String surname,
			String email, String studentTown, Long schoolId,
			Long courseCategoryId) {

		String query = "select distinct stud from Studente stud where stud in";
		String dummyQuery = "(select distinct stud from Studente stud)";
		String firstNameQuery = "(select distinct stud from Studente stud where stud.firstName = '"
				+ firstName + "')";
		String surnameQuery = "(select distinct stud from Studente stud where stud.surname = '"
				+ surname + "')";
		String emailQuery = "(select distinct stud from Studente stud join stud.emails email where email.email = '"
				+ email + "')";
		String studentTownQuery = "(select distinct stud from Studente stud where stud.indirizzoIt.citta = '"
				+ studentTown + "')";
		String schoolIdQuery = "(select distinct stud from Studente stud join stud.iscrizioni enrollment join enrollment.corso course join course.sedi branch where enrollment.studente = stud and branch.school.id = '"
				+ schoolId + "')";
		String courseCategoryIdQuery = "(select distinct stud from Studente stud join stud.iscrizioni enrollment where enrollment.studente = stud and enrollment.corso.categoria.id = '"
				+ courseCategoryId + "')";

		query += " ";
		query += dummyQuery;

		if (firstName != null && firstName != "") {
			query += " and stud in ";
			query += firstNameQuery;
		}

		if (surname != null && surname != "") {
			query += " and stud in ";
			query += surnameQuery;
		}

		if (email != null && email != "") {
			query += " and stud in ";
			query += emailQuery;
		}

		if (studentTown != null && studentTown != "") {
			query += " and stud in ";
			query += studentTownQuery;
		}

		if (schoolId != null && schoolId != 0L) {
			query += " and stud in ";
			query += schoolIdQuery;
		}

		if (courseCategoryId != null && courseCategoryId != 0L) {
			query += " and stud in ";
			query += courseCategoryIdQuery;
		}

		query += " order by stud.firstName";

		System.out.println(query);

		return getHibernateTemplate().find(query);
	}

	
	@Override
	public void update(Object o) {
		getHibernateTemplate().update(o);

	}

	
	@Override
	public BankBranch getBankBranch(Long id) {
		return getHibernateTemplate().get(BankBranch.class, id);
	}
}
